﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using BestBuy.Common;

namespace BestBuy.DAL
{
    public class CBuyerOffer
    {
        private TradeKeyDataContext context = new TradeKeyDataContext();
        private SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["TradeKeyConnectionString"].ConnectionString);
        public CBuyerOffer()
        { }

        public bool UpdateStatus(int idbangtin,int status)
        {
            var result = (from dl in context.BangTinMuas
                          where dl.IDBangTinMua == idbangtin
                          select dl).FirstOrDefault();
            result.TinhTrang = status;
            try
            {
                context.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }

        public int soLuongDuLieu(int status)
        {
            sqlCon.Open();

            string str = "select COUNT(*) from BangTinMua dl ";
            if (status != -2)
            {
                str = str + "where dl.TinhTrang=" + status.ToString();
            }
            SqlCommand cmd = new SqlCommand(str, sqlCon);
            int soLuong = Convert.ToInt32(cmd.ExecuteScalar());
            sqlCon.Close();
            return soLuong;
        }
        public int slTrangDuLieu(int soLuongTrenTrang, int status)
        {
            int soLuong = this.soLuongDuLieu(status);
            if (soLuong > 0)
            {
                int soTrang = soLuong / soLuongTrenTrang;
                if (soLuong % soLuongTrenTrang != 0)
                {
                    soTrang++;
                }

                return soTrang;
            }
            return -1;
        }

        public int[] FilterMaDuLieu(int status, int startPoint, int size)
        {
            DataTable dt = new DataTable();
            int[] arrMaDuLieu = new int[size];
            string sql = "select dl.IDBangTinMua,ROW_NUMBER() over (order by dl.IDBangTinMua desc) as row_num from BangTinMua dl ";
            if (status != -2)
            {
                sql = sql + "where  dl.TinhTrang=" + status.ToString();
            }
            string sql1 = "select distinct(t0.IDBangTinMua) from (" + sql + ") as t0 where t0.row_num between " + (startPoint + 1) + " and " + (startPoint + size);
            sqlCon.Open();
            SqlCommand cmd = new SqlCommand(sql1, sqlCon);
            SqlDataReader dr = cmd.ExecuteReader();
            int i = 0;
            while (dr.Read())
            {
                arrMaDuLieu[i] = int.Parse(dr["IDBangTinMua"].ToString());
                i++;
            }

            sqlCon.Close();

            return arrMaDuLieu;
        }

        public List<BangTinMua> GetList(int status, int begin, int size)
        {
            int[] arrMaDuLieu = this.FilterMaDuLieu(status, begin, size);

            var result = (from dl in context.BangTinMuas
                          where arrMaDuLieu.Contains(dl.IDBangTinMua)
                          orderby dl.IDBangTinMua descending
                          select dl).ToList();
            return result;
        }

        public BangTinMua GetBuyerOfferID(int id)
        {
            var q = (from sp in context.BangTinMuas
                     where sp.IDBangTinMua == id
                     select sp).FirstOrDefault();
            return q;
        }

        public List<BangTinMua> GetTopTen()
        {
            var q = (from sp in context.BangTinMuas
                     where sp.TinhTrang == (int)BangTinMuaStatus.Active
                     orderby sp.IDBangTinMua descending
                     select sp).Take(10).ToList();
            return q;
        }

        public int GetCountBuyOfID(int id)
        {
            int q = context.BangTinMuas.Where(x => x.IDTaiKhoan == id && x.TinhTrang == (int)BangTinMuaStatus.Active).Count();
            return q;
        }

        public List<BangTinMua> GetListOfIDAccount(int id, int begin, int size)
        {
            var result = (from dl in context.BangTinMuas
                          where dl.IDTaiKhoan == id && dl.TinhTrang == (int)BangTinMuaStatus.Active
                          orderby dl.IDBangTinMua descending
                          select dl).Skip(begin).Take(size).ToList();
            return result;
        }

        public int slTrangDuLieuOfIDCatalogy(int soLuongTrenTrang, int soLuong)
        {
            if (soLuong > 0)
            {
                int soTrang = soLuong / soLuongTrenTrang;
                if (soLuong % soLuongTrenTrang != 0)
                {
                    soTrang++;
                }

                return soTrang;
            }
            return -1;
        }

        public List<int> FilterMaDanhMucOfIDCatalogy(List<DanhMuc> list,int iddanhmuc)
        {
            DataTable dt = new DataTable();
            List<int> arrMaDuLieu = new List<int>();
            if (list.Count > 0)
            {
                var q = (from dl in context.DanhMucMuas
                         where list.Contains(dl.DanhMuc)
                         select dl.IDBangTinMua).ToList();
                arrMaDuLieu = q;
            }
            else
            {
                string sql = "select dl.IDBangTinMua from DanhMucMua dl where dl.IDDanhMuc = " + iddanhmuc.ToString();
                sqlCon.Open();
                SqlCommand cmd = new SqlCommand(sql, sqlCon);
                SqlDataReader dr = cmd.ExecuteReader();
                arrMaDuLieu.Add(int.Parse(iddanhmuc.ToString()));
                while (dr.Read())
                {
                    arrMaDuLieu.Add(int.Parse(dr["IDBangTinMua"].ToString()));
                }

                sqlCon.Close();
            }

            return arrMaDuLieu;
        }

        public List<BangTinMua> GetListOfIDCatalogy(List<DanhMuc> list,int iddanhmuc, int begin, int size)
        {
            List<int> arrMaDuLieu = this.FilterMaDanhMucOfIDCatalogy(list,iddanhmuc);
            var result = (from dl in context.BangTinMuas
                          where arrMaDuLieu.Contains(dl.IDBangTinMua) && dl.TinhTrang == (int)SanPhamStatus.Active
                          orderby dl.IDBangTinMua descending
                          select dl).Skip(begin).Take(size).ToList();
            return result;
        }

        public DanhMuc GetCatalogyOfIDBangTinMua(int id) {
            var q = (from dl in context.DanhMucMuas
                     where dl.IDBangTinMua == id
                     select dl).FirstOrDefault();
            DanhMuc dm = GetCatalogyOfID(q.IDDanhMuc);
            return dm;
        }

        private DanhMuc GetCatalogyOfID(int iddm) {
            var q = (from dl in context.DanhMucs
                     where dl.IDDanhMuc == iddm
                     select dl).FirstOrDefault();
            if (q.IDDanhMucCha != null) {
                q = GetCatalogyOfID(q.IDDanhMucCha.Value);
            }
            return q;
        }

        public List<int> GetCatalogyIDOfBangTinMuaRelate(int id)
        {
            var q = (from dl in context.DanhMucMuas
                     where dl.IDBangTinMua == id
                     select dl.IDDanhMuc).Distinct().ToList();
            return q;
        }

        public List<int> FilterMaDanhMucOfIDCatalogyRelate(List<int> list)
        {
            DataTable dt = new DataTable();
            List<int> arrMaDuLieu = new List<int>();
            var q = (from dl in context.DanhMucMuas
                     where list.Contains(dl.IDDanhMuc)
                     select dl.IDBangTinMua).Distinct().ToList();
            arrMaDuLieu = q;
            return arrMaDuLieu;
        }

        public List<BangTinMua> GetListOfIDCatalogyRelate(int idbangtin, int begin, int size)
        {
            List<int> arrMaDuLieu = this.FilterMaDanhMucOfIDCatalogyRelate(GetCatalogyIDOfBangTinMuaRelate(idbangtin));
            var result = (from dl in context.BangTinMuas
                          where arrMaDuLieu.Contains(dl.IDBangTinMua)
                          && dl.IDBangTinMua != idbangtin
                          && dl.TinhTrang == (int)SanPhamStatus.Active
                          orderby dl.IDBangTinMua descending
                          select dl).Distinct().Skip(begin).Take(size).ToList();
            return result;
        }

        public List<BangTinMua> Search(string strSearch, int IDCountry, int begin, int size)
        {
            List<BangTinMua> list = null;
            if (IDCountry > 0)
            {
                list = (from dl in context.BangTinMuas
                        where dl.TinhTrang == (int)SanPhamStatus.Active && dl.ThongTinTaiKhoan.IDQuocGia == IDCountry &&
                          (dl.TieuDe.Contains(strSearch) || dl.NoiDung.Contains(strSearch) || dl.TuKhoa.Contains(strSearch))
                        select dl).Skip(begin).Take(size).ToList();
            }
            else
            {
                list = (from dl in context.BangTinMuas
                        where dl.TinhTrang == (int)SanPhamStatus.Active &&
                          (dl.TieuDe.Contains(strSearch) || dl.NoiDung.Contains(strSearch) || dl.TuKhoa.Contains(strSearch))
                        select dl).Skip(begin).Take(size).ToList();
            }
            return list;
        }
    }
}